package com.css.zfzx.fwsjcj.modules.statistics.user.repository;

import com.css.zfzx.fwsjcj.modules.statistics.user.vo.DivisionVo;
import com.css.zfzx.fwsjcj.modules.statistics.user.vo.UserNumVo;
import com.css.zfzx.fwsjcj.modules.statistics.user.vo.UserVo;
import com.css.zfzx.fwsjcj.modules.statistics.user.vo.DivisionUserVo;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.HashMap;
import java.util.List;

/**
 * @Author leon
 * @Date: 2021/6/28 1:15 下午
 */
@Repository
public class UserQueryRepostoryImpl implements UserQueryRepository {
    @PersistenceContext
    EntityManager entityManager;

    @Override
    public HashMap<String, String> getAllDivisionName() {
        StringBuilder sql = new StringBuilder("select division_id divisionId, division_name divisionName from org_division where is_valid = '1' order by division_code DESC");

        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(new AliasToBeanResultTransformer(DivisionVo.class));

        List<DivisionVo> resultList = query.getResultList();
        HashMap<String, String> map = new HashMap<>(resultList.size());
        resultList.forEach(division -> {
            map.put(division.getDivisionId(), division.getDivisionName());
        });
        return map;
    }

    @Override
    public HashMap<String, DivisionVo> getAllDivision() {
        StringBuilder sql = new StringBuilder("select division_id divisionId, division_name divisionName, parent_id parentId, all_parent_id allParentId " +
                "from org_division where is_valid = '1' order by division_code DESC");

        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(new AliasToBeanResultTransformer(DivisionVo.class));

        List<DivisionVo> resultList = query.getResultList();
        HashMap<String, DivisionVo> map = new HashMap<>(resultList.size());
        resultList.forEach(division -> {
            map.put(division.getDivisionId(), division);
        });
        return map;
    }

    @Override
    public List<UserVo> getAllUser() {
        StringBuilder sql = new StringBuilder("select u.user_name userName, d.dept_name deptName, d.division_id divisionId, ds.division_name divisionName, ds.all_parent_id allParentId, ds.division_level divisionLevel from org_user u, org_dept_user du, org_dept d, org_division ds ");
        StringBuilder where = new StringBuilder(" where u.user_id = du.user_id and du.dept_id = d.dept_id and ds.division_id = d.division_id ");
        where.append("and u.is_valid =1 and du.is_valid =1 and d.is_valid =1 ");
        StringBuilder orderBy = new StringBuilder(" order by d.dept_name ");

        Query query = entityManager.createNativeQuery(sql.append(where).append(orderBy).toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(new AliasToBeanResultTransformer(UserVo.class));

        List<UserVo> resultList = query.getResultList();
        return resultList;
    }

    @Override
    public List<UserNumVo> getAllUserNum() {
        StringBuilder sql = new StringBuilder("select count(*) number, d.division_id divisionId from org_user u, org_dept_user du, org_dept d  ");
        StringBuilder where = new StringBuilder("where u.user_id = du.user_id and du.dept_id = d.dept_id ");
        where.append("and u.is_valid = 1 and du.is_valid =1 and d.is_valid =1  ");
        StringBuilder orderBy = new StringBuilder(" group by d.division_id ");

        Query query = entityManager.createNativeQuery(sql.append(where).append(orderBy).toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(new AliasToBeanResultTransformer(UserNumVo.class));

        List<UserNumVo> resultList = query.getResultList();
        return resultList;
    }


    @Override
    public  List<DivisionVo> getAllProvinceName() {
        StringBuilder sql = new StringBuilder("select division_id divisionId, division_name divisionName from org_division where is_valid = '1' and division_level='1' order by division_code DESC");

        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(new AliasToBeanResultTransformer(DivisionVo.class));

        List<DivisionVo> resultList = query.getResultList();
        return resultList;
    }

    @Override
    public BigInteger buildProvinceUserData(String divisionId) {
        StringBuilder builder = new StringBuilder();
        builder.append(" select count(user_id) count from org_user where user_id in(");
        builder.append(" select user_id from org_dept_user where dept_id in (");
        builder.append(" select dept_id from org_dept where division_id in (select division_id from org_division where all_parent_id like :divisionId) and dept_code not like 'JGG%')");
        builder.append(" and is_valid='1') and is_valid='1'");
        Query query = entityManager.createNativeQuery(builder.toString());
        query.setParameter("divisionId","%" +divisionId+"%" );
        BigInteger countNum = (BigInteger) query.getSingleResult();
//        if(resultList!=null && resultList.size()>0){
//            return resultList.get(0);
//        }

        return countNum ;
    }
}
